package com.examsys.dao;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.examsys.po.Admin;
import com.examsys.po.ExamDetail;
import com.examsys.po.ExamMain;
import com.examsys.po.Paper;
import com.examsys.po.PaperDetail;
import com.examsys.po.PaperSection;
import com.examsys.po.Question;

/**
 * 试卷数据访问层实现类
 * @author edu-1
 *
 */
public class PaperDaoImpl extends AbstractBaseDao<Paper, Integer> implements PaperDao {

	/**
	 * 添加试卷
	 * 
	 */
	@Override
	public void add(Paper obj) throws Exception {
		//构造插入语句
		String sql="Insert into PAPER (ID,ADMIN_ID,PAPER_NAME,START_TIME,END_TIME,PAPER_MINUTE,TOTAL_SCORE,POST_DATE,SHOW_SCORE,QORDER,STATUS,REMARK) values (?,?,?,?,?,?,?,?,?,?,?,?)";
		this.execute(sql, new Object[]{obj.getId(),obj.getAdmin().getId(),obj.getPaper_name(),obj.getStart_time(),obj.getEnd_time(),obj.getPaper_minute(),obj.getTotal_score(),obj.getPost_date(),obj.getShow_score(),obj.getQorder(),obj.getStatus(),obj.getRemark()});
	}

	/**
	 * 更新试卷
	 * 
	 */
	@Override
	public void update(Paper obj) throws Exception {
		//构造更新语句
		String sql="UPDATE PAPER SET ADMIN_ID=?,PAPER_NAME=?,START_TIME=?,END_TIME=?,PAPER_MINUTE=?,TOTAL_SCORE=?,POST_DATE=?,SHOW_SCORE=?,\"QORDER\"=?,STATUS=?,REMARK=? WHERE ID=?";
		this.execute(sql, new Object[]{obj.getAdmin().getId(),obj.getPaper_name(),obj.getStart_time(),obj.getEnd_time(),obj.getPaper_minute(),obj.getTotal_score(),obj.getPost_date(),obj.getShow_score(),obj.getQorder(),obj.getStatus(),obj.getRemark(),obj.getId()});
	}

	/**
	 * 删除试卷
	 * @param id 编号
	 */
	@Override
	public void delete(Integer id) throws Exception {
		//构建删除语句
		String sql="DELETE FROM PAPER WHERE ID=?";
		this.execute(sql, new Object[]{id});		
	}

	/**
	 * 获取试卷
	 * @param id 编号
	 */
	@Override
	public Paper get(Integer id) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.ADMIN_ID,a.PAPER_NAME,a.START_TIME,a.END_TIME,a.PAPER_MINUTE,a.TOTAL_SCORE,a.POST_DATE,a.SHOW_SCORE,a.QORDER,a.STATUS,a.REMARK,b.USER_NAME FROM PAPER a LEFT JOIN ADMIN b ON a.ADMIN_ID=b.ID WHERE a.ID=?";
		Map m = this.uniqueQuery(sql, new Object[]{id});//调用父类的查询方法拿数据
		
		Integer idd = (Integer)m.get("ID");//编号
		Integer paper_minute = (Integer)m.get("PAPER_MINUTE");//考试总时间
		Integer total_score = (Integer)m.get("TOTAL_SCORE");//考试总分数
		Integer qorder = (Integer)m.get("QORDER");//题目顺序
		String paper_name = (String)m.get("PAPER_NAME");//信息内容
		String status = (String)m.get("STATUS");//试卷状态
		String remark = (String)m.get("REMARK");//备注
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp start_time = (Timestamp)m.get("START_TIME");//开始考试时间
		Timestamp start_time2 = new Timestamp(start_time.getTime());//需要转换一下时间
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp end_time = (Timestamp)m.get("END_TIME");//结束考试时间
		Timestamp end_time2 = new Timestamp(end_time.getTime());//需要转换一下时间
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp post_date = (Timestamp)m.get("POST_DATE");//提交时间
		Timestamp post_date2 = new Timestamp(post_date.getTime());//需要转换一下时间
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp show_score = (Timestamp)m.get("SHOW_SCORE");//公布成绩时间
		Timestamp show_score2 = new Timestamp(show_score.getTime());//需要转换一下时间
		
		Paper paper=new Paper();//创建实体类对象
		paper.setId(idd);
		paper.setPaper_name(paper_name);
		paper.setPaper_minute(paper_minute);
		paper.setStart_time(start_time2);
		paper.setEnd_time(end_time2);
		paper.setShow_score(show_score2);
		paper.setPost_date(post_date2);
		paper.setTotal_score(total_score);
		paper.setQorder(qorder);
		paper.setStatus(status);
		paper.setRemark(remark);
		
		Integer admin_id = (Integer)m.get("ADMIN_ID");//管理员编号
		String user_name = (String)m.get("USER_NAME");//管理员名称
		
		Admin admin=new Admin();//创建实体类对象
		admin.setId(admin_id);
		admin.setUser_name(user_name);
		
		paper.setAdmin(admin);//设置关联对象
		
		//构建查询语句
		sql="SELECT a.ID,a.PAPER_ID,a.SECTION_NAME,a.PER_SCORE,a.REMARK,b.PAPER_NAME FROM PAPER_SECTION a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID WHERE a.PAPER_ID=?";
		List<Map> resultList = this.query(sql, new Object[]{id});//调用父类的查询方法拿数据
		List<PaperSection> paperSectionList=new ArrayList<PaperSection>();//存放PaperSection类型对象的集合
		
		for(Map mp:resultList){
			Integer idd1 = (Integer)mp.get("ID");//编号
			Integer per_score = (Integer)mp.get("PER_SCORE");//本章节的分数
			String section_name = (String)mp.get("SECTION_NAME");//章节名称
			String remark1 = (String)mp.get("REMARK");//备注
			
			PaperSection paperSection=new PaperSection();//创建实体类对象
			paperSection.setId(idd1);
			paperSection.setPer_score(per_score);
			paperSection.setSection_name(section_name);
			paperSection.setRemark(remark1);
			
			paperSection.setPaper(paper);//设置关联对象
			paperSectionList.add(paperSection);
		}
		
		//构建查询语句
		sql="SELECT a.ID,a.PAPER_ID,a.PAPER_SECTION_ID,a.QUESTION_ID,a.SCORE,a.PORDER,b.PAPER_NAME,c.SECTION_NAME,d.CONTENT FROM PAPER_DETAIL a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN PAPER_SECTION c ON a.PAPER_SECTION_ID=c.ID LEFT JOIN QUESTION d ON a.QUESTION_ID=d.ID WHERE a.PAPER_ID=?";
		
		resultList = this.query(sql, new Object[]{id});//调用父类的查询方法拿数据
		List<PaperDetail> paperDetailList =new ArrayList<PaperDetail> ();//存放ExamDetail类型对象的集合
		
		for(Map m3:resultList){
			Integer id1 =(Integer)m3.get("ID");//编号
			Double score =(Double)m3.get("SCORE");//本题分数
			Integer porder =(Integer)m3.get("PORDER");//排序号
			
			Integer paper_section_id =(Integer)m3.get("PAPER_SECTION_ID");//章节编号
			String  section_name =(String)m3.get("SECTION_NAME");//章节名称
			
			Integer question_id =(Integer)m3.get("QUESTION_ID");//试题编号
			String  content =(String)m3.get("CONTENT");//试题题干
			
			//创建试卷明细实体类对象
			PaperDetail paperDetail=new PaperDetail();
			paperDetail.setId(id1);//编号
			paperDetail.setScore(score);//本题分数
			paperDetail.setPorder(porder);//排序号
			
			paperDetail.setPaper(paper);//设置关联对象
			
			//创建章节实体类对象
			PaperSection paperSection=new PaperSection();
			paperSection.setId(paper_section_id);//章节编号
			paperSection.setSection_name(section_name);//章节名称
			paperDetail.setPaperSection(paperSection);//设置关联对象
			
			//创建试题实体类对象
			Question question=new Question();
			question.setId(question_id);//试题编号
			question.setContent(content);//试题题干
			paperDetail.setQuestion(question);//设置关联对象
		}
				
		paper.setPaperSections(paperSectionList);//关联章节
		paper.setPaperDetails(paperDetailList);//关联明细
		
		return paper;
	}

	/**
	 * 获取所有试卷
	 */
	@Override
	public List<Paper> getList() throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.ADMIN_ID,a.PAPER_NAME,a.START_TIME,a.END_TIME,a.PAPER_MINUTE,a.TOTAL_SCORE,a.POST_DATE,a.SHOW_SCORE,a.QORDER,a.STATUS,a.REMARK,b.USER_NAME FROM PAPER a LEFT JOIN ADMIN b ON a.ADMIN_ID=b.ID";
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Paper> list=new ArrayList<Paper>();//存放Paper类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer paper_minute = (Integer)m.get("PAPER_MINUTE");//考试总时间
			Integer total_score = (Integer)m.get("TOTAL_SCORE");//考试总分数
			Integer qorder = (Integer)m.get("QORDER");//题目顺序
			String paper_name = (String)m.get("PAPER_NAME");//信息内容
			String status = (String)m.get("STATUS");//试卷状态
			String remark = (String)m.get("REMARK");//备注
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp start_time = (Timestamp)m.get("START_TIME");//开始考试时间
			Timestamp start_time2 = new Timestamp(start_time.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp end_time = (Timestamp)m.get("END_TIME");//结束考试时间
			Timestamp end_time2 = new Timestamp(end_time.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp post_date = (Timestamp)m.get("POST_DATE");//提交时间
			Timestamp post_date2 = new Timestamp(post_date.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp show_score = (Timestamp)m.get("SHOW_SCORE");//公布成绩时间
			Timestamp show_score2 = new Timestamp(show_score.getTime());//需要转换一下时间
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(idd);
			paper.setPaper_name(paper_name);
			paper.setPaper_minute(paper_minute);
			paper.setStart_time(start_time2);
			paper.setEnd_time(end_time2);
			paper.setShow_score(show_score2);
			paper.setPost_date(post_date2);
			paper.setTotal_score(total_score);
			paper.setQorder(qorder);
			paper.setStatus(status);
			paper.setRemark(remark);
			
			Integer admin_id = (Integer)m.get("ADMIN_ID");//管理员编号
			String user_name = (String)m.get("USER_NAME");//管理员名称
			
			Admin admin=new Admin();//创建实体类对象
			admin.setId(admin_id);
			admin.setUser_name(user_name);
			
			paper.setAdmin(admin);//设置关联对象
			list.add(paper);
		}
		return list;
	}

	/**
	 * 带条件获取试卷
	 */
	@Override
	public List<Paper> getList(Paper obj) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.ADMIN_ID,a.PAPER_NAME,a.START_TIME,a.END_TIME,a.PAPER_MINUTE,a.TOTAL_SCORE,a.POST_DATE,a.SHOW_SCORE,a.QORDER,a.STATUS,a.REMARK,b.USER_NAME FROM PAPER a LEFT JOIN ADMIN b ON a.ADMIN_ID=b.ID WHERE 1=1";
		if(obj!=null){//条件构造
			
			if(obj.getId()!=null&&obj.getId()!=0){
				sql+=" AND a.ID="+obj.getId();
			}
			
			if(obj.getAdmin()!=null&&obj.getAdmin().getId()!=null
					&&obj.getAdmin().getId()!=0){
				sql+=" AND a.ADMIN_ID="+obj.getAdmin().getId();
			}
			
			if(obj.getPaper_name()!=null&&!"".equals(obj.getPaper_name())){
				sql+=" AND a.PAPER_NAME LIKE '%"+obj.getPaper_name()+"%'";
			}
			
		}

		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Paper> list=new ArrayList<Paper>();//存放Paper类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer paper_minute = (Integer)m.get("PAPER_MINUTE");//考试总时间
			Integer total_score = (Integer)m.get("TOTAL_SCORE");//考试总分数
			Integer qorder = (Integer)m.get("QORDER");//题目顺序
			String paper_name = (String)m.get("PAPER_NAME");//信息内容
			String status = (String)m.get("STATUS");//试卷状态
			String remark = (String)m.get("REMARK");//备注
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp start_time = (Timestamp)m.get("START_TIME");//开始考试时间
			Timestamp start_time2 = new Timestamp(start_time.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp end_time = (Timestamp)m.get("END_TIME");//结束考试时间
			Timestamp end_time2 = new Timestamp(end_time.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp post_date = (Timestamp)m.get("POST_DATE");//提交时间
			Timestamp post_date2 = new Timestamp(post_date.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp show_score = (Timestamp)m.get("SHOW_SCORE");//公布成绩时间
			Timestamp show_score2 = new Timestamp(show_score.getTime());//需要转换一下时间
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(idd);
			paper.setPaper_name(paper_name);
			paper.setPaper_minute(paper_minute);
			paper.setStart_time(start_time2);
			paper.setEnd_time(end_time2);
			paper.setShow_score(show_score2);
			paper.setPost_date(post_date2);
			paper.setTotal_score(total_score);
			paper.setQorder(qorder);
			paper.setStatus(status);
			paper.setRemark(remark);
			
			Integer admin_id = (Integer)m.get("ADMIN_ID");//管理员编号
			String user_name = (String)m.get("USER_NAME");//管理员名称
			
			Admin admin=new Admin();//创建实体类对象
			admin.setId(admin_id);
			admin.setUser_name(user_name);
			
			paper.setAdmin(admin);//设置关联对象
			list.add(paper);
		}
		return list;
	}

	/**
	 * 通过编号范围来获得试卷信息列表
	 * @param inIds
	 * @return
	 */
	public List<Paper> getList(String inIds)throws Exception{
		//构建查询语句
		String sql="SELECT a.ID,a.ADMIN_ID,a.PAPER_NAME,a.START_TIME,a.END_TIME,a.PAPER_MINUTE,a.TOTAL_SCORE,a.POST_DATE,a.SHOW_SCORE,a.QORDER,a.STATUS,a.REMARK,b.USER_NAME FROM PAPER a LEFT JOIN ADMIN b ON a.ADMIN_ID=b.ID WHERE 1=1";
		if(inIds!=null&&!"".equals(inIds)){//条件构造
			sql+=" AND a.ID IN ("+inIds+")";
		}

		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Paper> list=new ArrayList<Paper>();//存放Paper类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer paper_minute = (Integer)m.get("PAPER_MINUTE");//考试总时间
			Integer total_score = (Integer)m.get("TOTAL_SCORE");//考试总分数
			Integer qorder = (Integer)m.get("QORDER");//题目顺序
			String paper_name = (String)m.get("PAPER_NAME");//信息内容
			String status = (String)m.get("STATUS");//试卷状态
			String remark = (String)m.get("REMARK");//备注
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp start_time = (Timestamp)m.get("START_TIME");//开始考试时间
			Timestamp start_time2 = new Timestamp(start_time.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp end_time = (Timestamp)m.get("END_TIME");//结束考试时间
			Timestamp end_time2 = new Timestamp(end_time.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp post_date = (Timestamp)m.get("POST_DATE");//提交时间
			Timestamp post_date2 = new Timestamp(post_date.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp show_score = (Timestamp)m.get("SHOW_SCORE");//公布成绩时间
			Timestamp show_score2 = new Timestamp(show_score.getTime());//需要转换一下时间
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(idd);
			paper.setPaper_name(paper_name);
			paper.setPaper_minute(paper_minute);
			paper.setStart_time(start_time2);
			paper.setEnd_time(end_time2);
			paper.setShow_score(show_score2);
			paper.setPost_date(post_date2);
			paper.setTotal_score(total_score);
			paper.setQorder(qorder);
			paper.setStatus(status);
			paper.setRemark(remark);
			
			Integer admin_id = (Integer)m.get("ADMIN_ID");//管理员编号
			String user_name = (String)m.get("USER_NAME");//管理员名称
			
			Admin admin=new Admin();//创建实体类对象
			admin.setId(admin_id);
			admin.setUser_name(user_name);
			
			paper.setAdmin(admin);//设置关联对象
			list.add(paper);
		}
		return list;
	}
	/**
	 * 获得系列值的方法
	 */
	@Override
	public Integer getSeq() throws Exception {
		String sql="select PAPER_ID_SEQ.nextval SEQ from dual";
		Map map = this.uniqueQuery(sql, new Object[]{});
		Integer seq =(Integer) map.get("SEQ");
		return seq;
	}
}
